package com.yt.common.utils;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.http.MediaType;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/*
 * @author: chenyu.wang
 * @date: 2020-08-28
 * @description:Excel导出工具类，依赖于ClassUtil工具类
 */
public class ExcelExport2 {


    public static void export(HttpServletResponse response, List<?> importlist, String[] attributeNames, String name) {
        //声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet("列表");
        //设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 18);
        //获取对象属性
        Field[] fields = ClassUtil.getClassAttribute(importlist.get(0));
        //获取对象get方法
        List<Method> methodList = ClassUtil.getMethodGet(importlist.get(0));
        //循环字段名数组，创建标题行
        Row row = sheet.createRow(0);
        for (int j = 0; j < attributeNames.length; j++) {
            //创建列
            Cell cell = row.createCell(j);
            //设置单元类型为String
            cell.setCellType(CellType.STRING);
            cell.setCellValue(transCellType(attributeNames[j]));
        }
        //创建普通行
        for (int i = 0; i < importlist.size(); i++) {
            //因为第一行已经用于创建标题行，故从第二行开始创建
            row = sheet.createRow(i + 1);
            //如果是第一行就让其为标题行
            Object targetObj = importlist.get(i);
            for (int j = 0; j < fields.length; j++) {
                //创建列
                Cell cell = row.createCell(j);
                cell.setCellType(CellType.STRING);
                try {
                    Object value = methodList.get(j).invoke(targetObj);
                    cell.setCellValue(transCellType(value));
                } catch (IllegalAccessException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String fileName = sdf.format(new Date()) + name;
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
            response.setHeader("Cache-Control", "No-cache");
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    public static void export(HttpServletResponse response, List<?> importlist, String[] attributeNames, String name, String title, String money) {
        //声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet("列表");
        //设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 18);
        //获取对象属性
        Field[] fields = ClassUtil.getClassAttribute(importlist.get(0));
        //获取对象get方法
        List<Method> methodList = ClassUtil.getMethodGet(importlist.get(0));
        //第一标题
        Row row1 = sheet.createRow(0);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(title);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFCellStyle cellStyle1 = workbook.createCellStyle();
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        HSSFFont font = workbook.createFont();
//        font.
//        cellStyle.setFont();


        HSSFFont font = workbook.createFont();
        HSSFFont font1 = workbook.createFont();
        //是否加粗字体
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font1.setFontHeightInPoints((short) 12);
        Row row2 = sheet.createRow(1);
        Cell cell2 = row2.createCell(0);
        cell2.setCellValue(money);
        //循环字段名数组，创建标题行
        Row row = sheet.createRow(2);
        for (int j = 0; j < attributeNames.length; j++) {
            //创建列
            Cell cell = row.createCell(j);
            //设置单元类型为String
            cell.setCellType(CellType.STRING);
            cell.setCellValue(transCellType(attributeNames[j]));
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }
        //创建普通行
        for (int i = 0; i < importlist.size(); i++) {
            //因为第一行已经用于创建标题行，故从第二行开始创建
            row = sheet.createRow(i + 3);
            //如果是第一行就让其为标题行
            Object targetObj = importlist.get(i);
            for (int j = 0; j < fields.length; j++) {
                //创建列
                Cell cell = row.createCell(j);
                cell.setCellType(CellType.STRING);
                try {
                    Object value = methodList.get(j).invoke(targetObj);
                    cell.setCellValue(transCellType(value));
                    cell.setCellStyle(cellStyle1);
                } catch (IllegalAccessException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        //合并
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 12);
        sheet.addMergedRegion(cellRangeAddress);

        //font.setFontHeightInPoints(fontsize);
        //加载字体
        cellStyle.setFont(font);
        cellStyle2.setFont(font1);
        //居中
        cell1.setCellStyle(cellStyle2);
        // cell1.setRowStyle(cellStyle);
        cell2.setCellStyle(cellStyle);


        try {
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String fileName = sdf.format(new Date()) + name;
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
            response.setHeader("Cache-Control", "No-cache");
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /*
     * @description:内容格式化
     * @author: chenyu.wang
     * @date: 2020-08-28
     */
    private static String transCellType(Object value) {
        String str;
        if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            str = sdf.format(date);
        } else {
            str = String.valueOf(value);
            if (str.equals("null")) {
                str = "";
            }
        }
        return str;
    }
}
